package com.sl.au.repository;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;

import com.sl.au.entity.User;

import java.util.List;

public interface UserRepository extends JpaRepository<User, String> {
	@Query("select u from User u where u not in(select a.user from Account a )")
	List<User> findAccountIsNull();

	@Query("select u from User u where u.sex like %?1% or u.contactType like %?1% or u.name like %?1% ")
	Page<User> getUserName(String key, Pageable pageable);
	@Query("select u from User u where u.hierarchy.id=?2 and (u.sex like %?1% or u.contactType like %?1% or u.name like %?1%) ")
	Page<User> getUserNameByDeptWithKey(String key,String deptId ,Pageable pageable);
	@Query("select u from User u where u.hierarchy.id=?1 ")
	Page<User> getUserNameByDept(String deptId ,Pageable pageable);
	
	@Query("select count(*) from User u where u.hierarchy.id=?2 and (u.sex like %?1% or u.contactType like %?1% or u.name like %?1%) ")
	int getCountByDeptWithKey(String key,String deptId);

	@Query("select count(*) from User u where u.hierarchy.id=?1 ")
	int getCountByDept(String deptId);
	
	@Query("select u from User u where u.id " + "  not in( select a.user.id from Account a )")
	List<User> getNoAccount();

	User findByName(String name);
	
	User findByContactType(String contactType);

	@Query("select IFNULL( max(a.orderNum) , 0 ) + 1 from User a where a.hierarchy.id = ?1")
	int getUserOrder(String hierarchy);
	
	@Query("select u from User u where u.id in(select a.user.id from UserEventTypeBindInfo a where a.eventType.id=?1) and u.id in(select b.user_id from ViewForUserGridInfo b where b.grid_id=?2)")
	List<User> findUsersByEventTypeAndGrid(String eventTypeId,String gridId);

	@Query("select u from User u where u.id in(select a.user.id from UserEventTypeBindInfo a where a.eventType.id=?1) and u.id in(select b.user_id from ViewForUserGridInfo b where b.grid_id=?2)")
	Page<User> findUsersByEventTypeAndGrid(String eventTypeId,String gridId, Pageable pageable);

	@Query("select count(1) from User u where u.id in(select a.user.id from UserEventTypeBindInfo a where a.eventType.id=?1) and u.id in(select b.user_id from ViewForUserGridInfo b where b.grid_id=?2)")
	int findUsersByEventTypeAndGridCount(String eventTypeId,String gridId);
	
	@Query("select a from User a where a.grading is null and ( a.name like %?1% )")
	Page<User> getByNoGradingAndKey(String key, Pageable pageable);

	@Query("select a from User a where a.grading is null")
	Page<User> getAllByNoGrading(Pageable pageable);

	@Query("select a from User a where a.grading.id = ?1 and ( a.name like %?2% )")
	Page<User> getByGradingAndKey(String gradingId, String key, Pageable pageable);

	@Query("select a from User a where a.grading.id = ?1")
	Page<User> getAllByGrading(String gradingId, Pageable pageable);

	@Query("select a from User a join a.grids g where g.id=?1")
	Page<User> getBindUsersPage(String key, Pageable pageable);

	@Query("select a from User a join a.grids g where g.id=?1")
	List<User> getBindUsersList(String key);

	@Query("select a from User a where a.hierarchy.id=?1")
	Page<User> getBindHierarchyUsersPageBykey(String key, Pageable pageable);

	@Query("select a from User a where a.hierarchy is null and (a.sex like %?1% or a.name like %?1%)")
	Page<User> getUnbindHierarchyUsersPageBykey(String key, Pageable pageable);

	@Query("select a from User a where a.hierarchy is null")
	Page<User> getUnbindHierarchyUsersPage(Pageable pageable);

	@Query("select a from User a where a.hierarchy.id=?1 and (a.sex like %?2% or a.name like %?2%)")
	Page<User> getBindHierarchyUsersPageBykeyAndCon(String key, String con, Pageable pageable);

	@Query("select a from User a join a.grids g where g.id=?1 and (a.sex like %?2% or a.name like %?2%)")
	Page<User> getBindUsersPageByKeyAndCon(String gridId, String con, Pageable pageable);

	@Query("select a from User a where a.hierarchy is not null and (a.sex like %?1% or a.name like %?1%)")
	Page<User> getAllBindHierarchyUsersByKeyAndCon(String con, Pageable pageable);

	@Query("select a from User a where a.hierarchy is not null")
	Page<User> getBindHierarchyUsersPageNone(Pageable pageable);

	@Query("select a from User a where a.hierarchy is not null and (a.sex like %?1% or a.name like %?1%)")
	Page<User> findBindByConOnly(String con, Pageable pageable);

	@Query("select a from User a where a.hierarchy.id !=?1 or a.hierarchy is null and (a.sex like %?1% or a.name like %?1%)")
	Page<User> findUsersUnbindHierarchyByKeyAndCon(String key, String con, Pageable pageable);

	@Query("select a from User a where a.hierarchy.id !=?1 or a.hierarchy is null")
	Page<User> findUserUnbindHierarchyOnlyKey(String key, Pageable pageable);

	@Query("select a from User a where a.hierarchy is null and (a.sex like %?1% or a.name like %?1%)")
	Page<User> findUsersUnbindHierarchyOnlyCon(String con, Pageable pageable);

	@Query("select a from User a where a.hierarchy is null")
	Page<User> findUsersUnbindHierarchyNone(Pageable pageable);

	/********* 用户清除 组织信息 **********/
	@Modifying
	@Query("update User a set a.hierarchy = null, a.order = null, a.orderNum = 0  where a.hierarchy.id in (?1)")
	int deleteHierarchy(String[] ids);

	@Query("select u from User u where u.id not in(select a.user.id from UserEventTypeBindInfo a) and(u.sex like %?1% or u.name like %?1%)")
	Page<User> findUsersUnbindEventTypeOnlyCon(Pageable pageable, String con);

	@Query("select u from User u where u.id not in(select a.user.id from UserEventTypeBindInfo a)")
	Page<User> findUsersUnbindEventTypeNone(Pageable pageable);

	@Query("select u from User u where u.id not in(select a.user.id from UserEventTypeBindInfo a where a.eventType.id=?1) and(u.sex like %?2% or u.name like %?2%)")
	Page<User> findUsersUnbindEventTypeByKeyAndCon(Pageable pageable, String key, String con);

	@Query("select u from User u where u.id not in(select a.user.id from UserEventTypeBindInfo a where a.eventType.id=?1)")
	Page<User> findUsersUnbindEventTypeOnlyKey(Pageable pageable, String key);

	@Query("select u from User u where u.id in(select a.user.id from UserEventTypeBindInfo a) and(u.sex like %?1% or u.name like %?1%)")
	Page<User> findUsersBindEventTypeOnlyCon(Pageable pageable, String con);

	@Query("select u from User u where u.id in(select a.user.id from UserEventTypeBindInfo a)")
	Page<User> findUsersBindEventTypeNone(Pageable pageable);

	@Query("select u from User u where u.id  in(select a.user.id from UserEventTypeBindInfo a where a.eventType.id=?1) and(u.sex like %?2% or u.name like %?2%)")
	Page<User> findUsersBindEventTypeByKeyAndCon(Pageable pageable, String key, String con);

	@Query("select u from User u where u.id  in(select a.user.id from UserEventTypeBindInfo a where a.eventType.id=?1)")
	Page<User> findUsersBindEventTypeOnlyKey(Pageable pageable, String key);
	
	/****************** 获取模板中人员信息 ******************/
	@Query("select a from User a where exists (from TemplateDetails t where t.template.id = ?1 and t.grading.id = ?2 and t.user = a) ")
	Page<User> getTemplateSetUserPage(String templateId, String gradingId, Pageable pageable);

	@Query("select a from User a where exists (from TemplateDetails t where t.template.id = ?1 and t.grading.id = ?2 and t.user = a) and a.name like %?3%")
	Page<User> getTemplateSetUserPageByKey(String templateId, String gradingId, String key, Pageable pageable);

	/****************** 获取模板中未设置人员信息 ******************/
	@Query("select a from User a where not exists (from TemplateDetails t where t.template.id = ?1 and t.user = a) ")
	Page<User> getTemplateNotSetUserPage(String templateId, Pageable pageable);
	
	@Query("select a from User a where not exists (from TemplateDetails t where t.template.id = ?1 and t.user = a) and a.name like %?2%")
	Page<User> getTemplateNotSetUserPageByKey(String templateId, String key, Pageable pageable);

	/****************** 获取网格评分人员信息 ******************/
	@Query("select a from User a where exists (from GridUserDetails t where t.grid.id = ?1 and t.grading.id = ?2 and t.user = a) ")
	Page<User> getGridUserDetailsSetUserPage(String templgridIdateId, String gradingId, Pageable pageable);
	
	@Query("select a from User a where exists (from GridUserDetails t where t.grid.id = ?1 and t.grading.id = ?2 and t.user = a) and a.name like %?3%")
	Page<User> getGridUserDetailsSetUserPageByKey(String gridId, String gradingId, String key, Pageable pageable);
	
	/****************** 获取网格评分中未设置人员信息 ******************/
	@Query("select a from User a where not exists (from GridUserDetails t where t.grid.id = ?1 and t.user = a) ")
	Page<User> getGridUserDetailsNotSetUserPage(String gridId, Pageable pageable);
	
	@Query("select a from User a where not exists (from GridUserDetails t where t.grid.id = ?1 and t.user = a) and a.name like %?2%")
	Page<User> getGridUserDetailsNotSetUserPageByKey(String gridId, String key, Pageable pageable);
	
	/****************** 获取组织下人员信息 ******************/
	@Query("select a from User a where a.hierarchy.id=?1")
	List<User> getUsersByHierarchy(String hierarchyId);
	
	@Query("select a from User a where a.name like %?1%")
	List<User> getUsersByName(String name);
}
